/*--------------------------------------------------------------------------------
 * Adaptive Intelligence - Foundation Framework
 * Version 2.10
 * 
 * Stored Procedure Base Class
 *      Provides a base definition for defining and implementing a stored procedure.
 *------------------------------------------------------------------------------*/

/*--------------------------------------------------------------------------------
 * System Imports
 *------------------------------------------------------------------------------*/
using System;
using System.Data;
using Adaptive.Foundation.Properties;

namespace Adaptive.Foundation.Data
{
    /// <summary>
    /// Provides a base definition for defining and implementing a stored procedure.
    /// </summary>
    /// <remarks>
    /// This class defines the base methodology for wrapping a stored procedure call
    /// in a managed container.
    /// </remarks>
    public abstract class StoredProcedureBase : DisposableObjectBase
    {
        /*--------------------------------------------------------------------------------
         * Public Event Definitions
         *------------------------------------------------------------------------------*/
        #region Public Event Definitions
        /// <summary>
        /// Occurs once the object is ready to execute, and before execution begins.
        /// </summary>
        public event EventHandler PrepareForExecution;
        /// <summary>
        /// Occurs when the execution completes, regardless of result.
        /// </summary>
        public event EventHandler CompleteExecution;
        /// <summary>
        /// Occurs when a data exception is caught.
        /// </summary>
        public event DataExceptionEventHandler DataException;
        /// <summary>
        /// Occurs when a data connection attempt fails.
        /// </summary>
        public event ExceptionEventHandler ConnectionFailure;
        /// <summary>
        /// Occurs when a locally defined parameter has no data and needs to request a value
        /// from the caller.
        /// </summary>
        public event SqlParameterEventHandler ParameterDataRequest;
        /// <summary>
        /// Occurs when a new parameter instance is created.
        /// </summary>
        public event SqlParameterEventHandler ParameterCreated;
        /// <summary>
        /// Occurs when a parameter instance is removed.
        /// </summary>
        public event SqlParameterEventHandler ParameterRemoved;
        /// <summary>
        /// Occurs when a transaction is rolled back due to an exception.
        /// </summary>
        public event ExceptionEventHandler TransactionRollback;
        #endregion

        /*--------------------------------------------------------------------------------
		 * Private Member Declarations
		 *------------------------------------------------------------------------------*/
        #region Private Member Declarations
        /// <summary>
        /// The data provider instance.
        /// </summary>
        private IDataProvider _provider;
        /// <summary>
        /// The name of the stored procedure to execute.
        /// </summary>
        private string _procedureName = string.Empty;
        /// <summary>
        /// List of modified fields populated from data class.
        /// </summary>
        private string _modifiedFieldsList = string.Empty;
        /// <summary>
        /// List of parameters defined for the stored procedure.
        /// </summary>
        private DataParameterCollection _parameterList;
        /// <summary>
        /// The expected result type.
        /// </summary>
        private StoredProcedureResultType _result = StoredProcedureResultType.None;
        #endregion

        /*--------------------------------------------------------------------------------
         * Constructor / Dispose / Destructor Methods
		 *------------------------------------------------------------------------------*/
        #region Constructor / Dispose / Destructor Methods
        /// <summary>
        /// Initializes a new instance of the class.
        /// </summary>
        /// <param name="procedureName">
        /// The name of the stored procedure.
        /// </param>
        /// <param name="provider">
        /// An <see cref="IDataProvider"/> instance used to create platform-specific
        /// data objects.
        /// </param>
        /// <exception cref="ArgumentNullException">
        /// Occurs if the <i>procedureName</i> parameter is null or empty.
        /// </exception>
        /// <exception cref="ArgumentNullException">
        /// Occurs if the <i>provider</i> parameter is null.
        /// </exception>
        protected StoredProcedureBase(string procedureName, IDataProvider provider)
            : base()
        {
            if ((procedureName == null) || (procedureName.Length == 0))
                throw new ArgumentNullException(Resources.ProcedureNameArgumentName);
            if (provider == null)
                throw new ArgumentNullException(Resources.ProviderArgumentName);
            _procedureName = procedureName;
            _provider = provider;
            _parameterList = new DataParameterCollection();
            CreateParameters();
        }
        /// <summary>
        /// Initializes a new instance of the class.
        /// </summary>
        /// <param name="procedureName">
        /// The name of the stored procedure.
        /// </param>
        /// <param name="provider">
        /// An <see cref="IDataProvider"/> instance used to create platform-specific
        /// data objects.
        /// </param>
        /// <param name="result">
        /// A <see cref="StoredProcedureResultType"/> enumerated value indicating the type of resuilt the 
        /// stored procedure is expected to produce.
        /// </param>
        /// <exception cref="ArgumentNullException">
        /// Occurs if the <i>procedureName</i> parameter is null or empty.
        /// </exception>
        /// <exception cref="ArgumentNullException">
        /// Occurs if the <i>provider</i> parameter is null.
        /// </exception>
        protected StoredProcedureBase(string procedureName, IDataProvider provider, StoredProcedureResultType result)
            : base()
        {
            if ((procedureName == null) || (procedureName.Length == 0))
                throw new ArgumentNullException(Resources.ProcedureNameArgumentName);
            if (provider == null)
                throw new ArgumentNullException(Resources.ProviderArgumentName);

            _procedureName = procedureName;
            _provider = provider;
            _result = result;
            _parameterList = new DataParameterCollection();
            CreateParameters();
        }
        /// <summary>
        /// Deallocates internally allocated objectts.
        /// </summary>
        /// <param name="disposing">
        /// A value indicating whether the object is being disposed.
        /// </param>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        protected override void Dispose(bool disposing)
        {
            try
            {
                if ((!Disposed) && (disposing))
                {
                    if (_parameterList != null)
                    {
                        _parameterList.ClearAndDispose();
                        _parameterList.Dispose();
                    }
                }
            }
            catch
            {
            }
            finally
            {
                _parameterList = null;
                _provider = null;
                _procedureName = null;
            }
            base.Dispose(disposing);
        }

        #endregion

        /*--------------------------------------------------------------------------------
	     * Public Properties
		 *------------------------------------------------------------------------------*/
        #region Public Properties
        /// <summary>
        /// Gets or sets the string list of fields that are to be modified.
        /// </summary>
        /// <value>
        /// The list of fields to record in the audit statement.
        /// </value>
        public string ModifiedFieldsList
        {
            get { return _modifiedFieldsList; }
            set
            {
                _modifiedFieldsList = value;
            }
        }
        /// <summary>
        /// Gets or sets the name of the stored procedure to be executed.
        /// </summary>
        /// <value>
        /// A valid store procedure name.
        /// </value>
        public string Name
        {
            get { return _procedureName; }
            set { _procedureName = value; }
        }
        /// <summary>
        /// Gets the reference to the list of parameters defined for the 
        /// stored procedure.
        /// </summary>
        /// <value>
        /// A <see cref="DataParameterCollection"/> instance.
        /// </value>
        /// <remarks>
        /// It is not recommended to add or remove items directly from this collection;
        /// rather the <b>AddParameter</b> and <b>RemoveParameter</b> methods should
        /// be used.
        /// </remarks>
        public DataParameterCollection Parameters
        {
            get { return _parameterList; }
        }
        /// <summary>
        /// Gets or sets the type of result for the stored procedure.
        /// </summary>
        /// <value>
        /// A <see cref="StoredProcedureResultType"/> enumerated value indicating the type of 
        /// result expected.
        /// </value>
        public StoredProcedureResultType ResultType
        {
            get { return _result; }
            set { _result = value; }
        }
        /// <summary>
        /// Gets or sets the value of the SQL parameter at the specified index.
        /// </summary>
        /// <remarks>
        /// This is the indexer property for the class, used to quickly access and assign
        /// values to the parameters for a procedure.
        /// </remarks>
        /// <param name="index">
        /// The ordinal index of the parameter to be read or set.
        /// </param>
        /// <value>
        /// A value to be assigned to a SQL parameter instance, or <b>null</b> if the 
        /// index is invalid.
        /// </value>
        public object this[int index]
        {
            get
            {
                object returnValue = null;

                if (_parameterList[index] != null)
                    returnValue = _parameterList[index].Value;

                return returnValue;
            }
            set
            {
                if (_parameterList[index] != null)
                {
                    try
                    {
                        _parameterList[index].Value = value;
                    }
                    catch { }
                }
            }
        }
        /// <summary>
        /// Gets or sets the value of the SQL parameter with the specified parameter name.
        /// </summary>
        /// <remarks>
        /// This is the indexer property for the class, used to quickly access and assign
        /// values to the parameters for a procedure.
        /// </remarks>
        /// <param name="parameterName">
        /// The name of the parameter to be read or set.
        /// </param>
        /// <value>
        /// A value to be assigned to a SQL parameter instance, or <b>null</b> if the 
        /// index is invalid.
        /// </value>
        public object this[string parameterName]
        {
            get
            {
                object returnValue = null;

                if (_parameterList[parameterName] != null)
                    returnValue = _parameterList[parameterName].Value;

                return returnValue;
            }
            set
            {
                if (_parameterList[parameterName] != null)
                {
                    try
                    {
                        _parameterList[parameterName].Value = value;
                    }
                    catch { }
                }
            }
        }
        #endregion

        /*--------------------------------------------------------------------------------
         * Protected Methods / Functions / Overrides
		 *------------------------------------------------------------------------------*/
        #region Protected Methods / Functions / Overrides
        /// <summary>
        /// Defines and instantiates the parameter instances for a specific stored procedure instance.
        /// </summary>
        /// <remarks>
        /// This is called from the constructor.  
        /// 
        /// Notes to Implementors:  This may be used when creating a class definition for a specific
        /// stored procedure instance.  When implemeting this method, the <b>AddParameter</b> 
        /// methods should be used.
        /// </remarks>
        protected virtual void CreateParameters()
        {
        }
        #endregion

        /*--------------------------------------------------------------------------------
         * Protected Event Methods / Event Methods
		 *------------------------------------------------------------------------------*/
        #region Protected Event Methods / Event Methods
        /// <summary>
        /// Raises the <see cref="PrepareForExecution"/> event.
        /// </summary>
        /// <param name="e">
        /// An <see cref="EventArgs"/> event arguments instance.
        /// </param>
        protected virtual void OnPrepareForExecution(EventArgs e)
        {
            if (PrepareForExecution != null)
                PrepareForExecution(this, e);
        }
        /// <summary>
        /// Raises the <see cref="CompleteExecution"/> event.
        /// </summary>
        /// <param name="e">
        /// An <see cref="EventArgs"/> event arguments instance.
        /// </param>
        protected virtual void OnCompleteExecution(EventArgs e)
        {
            if (CompleteExecution != null)
                CompleteExecution(this, e);
        }
        /// <summary>
        /// Raises the <see cref="DataException"/> event.
        /// </summary>
        /// <param name="e">
        /// A <see cref="DataExceptionEventArgs"/> event arguments instance 
        /// containing the data exception that was caught.
        /// </param>
        protected virtual void OnDataException(DataExceptionEventArgs e)
        {
            if (DataException != null)
                DataException(this, e);
        }
        /// <summary>
        /// Raises the <see cref="ConnectionFailure"/> event.
        /// </summary>
        /// <param name="e">
        /// An <see cref="ExceptionEventArgs"/> event arguments instance 
        /// containing the exception that was caught.
        /// </param>
        protected virtual void OnConnectionFailure(ExceptionEventArgs e)
        {
            if (ConnectionFailure != null)
                ConnectionFailure(this, e);
        }
        /// <summary>
        /// Raises the <see cref="ParameterDataRequest"/> event.
        /// </summary>
        /// <param name="e">
        /// A <see cref="SqlParameterEventArgs"/> event arguments instance 
        /// containing the details of the specific SQL parameter instance.
        /// </param>
        protected virtual void OnParameterDataRequest(SqlParameterEventArgs e)
        {
            if (ParameterDataRequest != null)
                ParameterDataRequest(this, e);
        }
        /// <summary>
        /// Raises the <see cref="OnParameterCreated"/> event.
        /// </summary>
        /// <param name="e">
        /// A <see cref="SqlParameterEventArgs"/> event arguments instance 
        /// containing the details of the specific SQL parameter instance.
        /// </param>
        protected virtual void OnParameterCreated(SqlParameterEventArgs e)
        {
            if (ParameterCreated != null)
                ParameterCreated(this, e);
        }
        /// <summary>
        /// Raises the <see cref="ParameterRemoved"/> event.
        /// </summary>
        /// <param name="e">
        /// A <see cref="SqlParameterEventArgs"/> event arguments instance 
        /// containing the details of the specific SQL parameter instance.
        /// </param>
        protected virtual void OnParameterRemoved(SqlParameterEventArgs e)
        {
            if (ParameterRemoved != null)
                ParameterRemoved(this, e);
        }
        /// <summary>
        /// Raises the <see cref="E:TransactionRollback"/> event.
        /// </summary>
        /// <param name="e">
        /// The <see cref="ExceptionEventArgs"/> instance 
        /// containing the event data.</param>
        protected virtual void OnTransactionRollback(ExceptionEventArgs e)
        {
            if (TransactionRollback != null)
                TransactionRollback(this, e);
        }
        #endregion

        /*--------------------------------------------------------------------------------
         * Public Methods / Functions
		 *------------------------------------------------------------------------------*/
        #region Public Methods / Functions
        /// <summary>
        /// Creates and adds a new parameter instance to the local collection.
        /// </summary>
        /// <param name="parameterName">The name of the parameter.</param>
        /// <param name="dataType">The data type for the parameter,</param>
        /// <returns>
        /// An <see cref="IDataParameter"/> as defined by the internal provider instance, or <b>null</b> 
        /// if the parameter has already been defined.
        /// </returns>
        public virtual IDataParameter AddParameter(string parameterName, DbType dataType)
        {
            return AddParameter(parameterName, dataType, true);
        }
        /// <summary>
        /// Creates and adds a new parameter instance to the local collection.
        /// </summary>
        /// <param name="parameterName">The name of the parameter.</param>
        /// <param name="dataType">The data type for the parameter,</param>
        /// <param name="allowNull">A value indicating whether the parameter is nullable.</param>
        /// <returns>
        /// An <see cref="IDataParameter"/> as defined by the internal provider instance, or <b>null</b> 
        /// if the parameter has already been defined.
        /// </returns>
        public virtual IDataParameter AddParameter(string parameterName, DbType dataType, bool allowNull)
        {
            IDataParameter newParameter = null;         //New parameter instance.
            SqlParameterEventArgs evArgs = null;        //Event arguments.

            //Do nothing if the item already exists.
            if (_parameterList[parameterName] == null)
            {
                //Create the parameter instance, populate, and add to the list.
                newParameter = _provider.CreateParameterInstance();
                newParameter.ParameterName = parameterName;
                newParameter.DbType = dataType;
                _parameterList.Add(newParameter);

                //Raise the event.
                evArgs = new SqlParameterEventArgs(newParameter);
                OnParameterCreated(evArgs);
            }

            return newParameter;
        }
        /// <summary>
        /// Executes the stored procedure.
        /// </summary>
        /// <param name="dataSource">
        /// A <see cref="IDbConnection"/> instance representing a valid data source.
        /// </param>
        /// <returns>
        /// A <see cref="StoredProcedureResult"/> instance defining the result of the stored
        /// procedure execution.
        /// </returns>
        public virtual StoredProcedureResult Execute(IDbConnection dataSource)
        {
            StoredProcedureResult returnValue = null;       //Return value.
            IDataAdapter adapter = null;                    //The data adapter instance to use to transform data.
            IDbCommand command = null;                      //The data command to be executed.
            IDbTransaction transaction = null;              //The data transaction wrapper.
            SqlParameterEventArgs paramEvArgs = null;       //Parameter event arguments.

            //Create return value.
            returnValue = new StoredProcedureResult(_result);
            OnPrepareForExecution(EventArgs.Empty);

            //Try to ensure there is a connection, first.
            returnValue.Success = TryConnect(dataSource);
            if (returnValue.Success)
            {
                //Create the transaction, adapter and command instances.
                transaction = _provider.CreateTransactionInstance(dataSource);
                command = _provider.CreateCommand(dataSource);
                command.CommandText = _procedureName;
                command.CommandType = CommandType.StoredProcedure;
                command.Transaction = transaction;

                adapter = _provider.CreateAdapter(command);

                //Add the parameters to the command.
                _parameterList.MoveFirst();
                while (!_parameterList.Eof)
                {
                    //Validate the parameter content - request data when not populated.
                    if ((!_parameterList.Value.IsNullable) && (_parameterList.Value.Value == null))
                    {
                        paramEvArgs = new SqlParameterEventArgs(_parameterList.Value);
                        OnParameterDataRequest(paramEvArgs);
                    }

                    //Add to the list.
                    if (_parameterList.Value.Value == null)
                        _parameterList.Value.Value = System.DBNull.Value;

                    command.Parameters.Add(_parameterList.Value);
                    _parameterList.MoveNext();
                }

                //Execute based on type of result expected.
                switch (_result)
                {
                    case StoredProcedureResultType.DataSet:
                        ExecuteForDataset(ref returnValue, adapter, transaction);
                        break;

                    case StoredProcedureResultType.DataTable:
                        ExecuteForDataTable(ref returnValue, adapter, transaction);
                        break;

                    case StoredProcedureResultType.RowCount:
                        ExecuteForRowCount(ref returnValue, command);
                        break;

                    case StoredProcedureResultType.None:
                        ExecuteNonQuery(ref returnValue, command);
                        break;
                }

                //Close the data connection.
                TryClose(dataSource);
            }

            //Finish task.
            OnCompleteExecution(EventArgs.Empty);
            return returnValue;
        }
        /// <summary>
        /// Removes the specified parameter from the local list.
        /// </summary>
        /// <param name="index">
        /// The ordinal index of the parameter to be removed.
        /// </param>
        public virtual void RemoveParameter(int index)
        {
            SqlParameterEventArgs evArgs = null;        //Event arguments.

            if (_parameterList[index] != null)
            {
                evArgs = new SqlParameterEventArgs(_parameterList[index]);
                _parameterList.Remove(index);
                OnParameterRemoved(evArgs);
            }
        }
        /// <summary>
        /// Removes the specified parameter from the local list.
        /// </summary>
        /// <param name="name">
        /// The name of the parameter to be removed.
        /// </param>
        public virtual void RemoveParameter(string name)
        {
            SqlParameterEventArgs evArgs = null;        //Event arguments.

            if (_parameterList[name] != null)
            {
                evArgs = new SqlParameterEventArgs(_parameterList[name]);
                _parameterList.Remove(name);
                OnParameterRemoved(evArgs);
            }
        }
        #endregion

        /*--------------------------------------------------------------------------------
         * Private Methods / Functions
		 *------------------------------------------------------------------------------*/
        #region Private Methods / Functions
        /// <summary>
        /// Executes the stored procedure, expecting multiple resultsets.
        /// </summary>
        /// <param name="result">
        /// A <see cref="StoredProcedureResult"/> where the results are to be stored.
        /// </param>
        /// <param name="adapter">
        /// The data adapter instance to be used.
        /// </param>
        /// <param name="transaction">
        /// The current transaction instasnce.
        /// </param>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        private void ExecuteForDataset(ref StoredProcedureResult result, IDataAdapter adapter, IDbTransaction transaction)
        {
            DataSet resultingDataset = null;

            try
            {
                resultingDataset = new DataSet();
                adapter.Fill(resultingDataset);
                transaction.Commit();
                result.Success = true;
                result.DatasetResult = resultingDataset;

            }
            catch (Exception ex)
            {
                transaction.Rollback();
                OnTransactionRollback(new ExceptionEventArgs(ex));
                OnDataException(new DataExceptionEventArgs(ex));
                result.Success = false;
                result.LastException = ex;
                resultingDataset.Dispose();
            }
        }
        /// <summary>
        /// Executes the stored procedure, expecting a single resultset.
        /// </summary>
        /// <param name="result">
        /// A <see cref="StoredProcedureResult"/> where the results are to be stored.
        /// </param>
        /// <param name="adapter">
        /// The data adapter instance to be used.
        /// </param>
        /// <param name="transaction">
        /// The current transaction instance.
        /// </param>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        private void ExecuteForDataTable(ref StoredProcedureResult result, IDataAdapter adapter, IDbTransaction transaction)
        {
            DataSet resultingDataset = null;
            DataTable resultingTable = null;
            DataTable sourceTable = null;

            resultingDataset = new DataSet();

            try
            {

                adapter.Fill(resultingDataset);
                transaction.Commit();
                if (resultingDataset.Tables.Count > 0)
                {
                    sourceTable = resultingDataset.Tables[0];
                    resultingTable = sourceTable.Copy();

                }
                result.Success = true;
                result.DataTableResult = resultingTable;

            }
            catch (Exception ex)
            {
                transaction.Rollback();
                OnDataException(new DataExceptionEventArgs(ex));
                result.Success = false;
                result.LastException = ex;

            }
            resultingDataset.Dispose();
        }
        /// <summary>
        /// Executes the stored procedure, expecting the rows affected count.
        /// </summary>
        /// <param name="result">
        /// A <see cref="StoredProcedureResult"/> where the results are to be stored.
        /// </param>
        /// <param name="command">
        /// The data command instance to be used.
        /// </param>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        private void ExecuteForRowCount(ref StoredProcedureResult result, IDbCommand command)
        {
            int rowsAffected = 0;               //Row count result.
            IDbTransaction transaction = null;  //Transaction pointer.

            try
            {
                transaction = command.Transaction;

                //Attempt to execute.
                rowsAffected = command.ExecuteNonQuery();
                result.Success = true;
                result.RowsAffected = rowsAffected;
                transaction.Commit();
                //else the transaction is already rolled back.
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                OnTransactionRollback(new ExceptionEventArgs(ex));
                OnDataException(new DataExceptionEventArgs(ex));
                result.Success = false;
                result.LastException = ex;

            }
        }
        /// <summary>
        /// Executes the stored procedure, expecting no result.
        /// </summary>
        /// <param name="result">
        /// A <see cref="StoredProcedureResult"/> where the results are to be stored.
        /// </param>
        /// <param name="command">
        /// The data command instance to be used.
        /// </param>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        private void ExecuteNonQuery(ref StoredProcedureResult result, IDbCommand command)
        {
            IDbTransaction transaction = null;  //Transaction pointer.

            transaction = command.Transaction;
            try
            {
                command.ExecuteNonQuery();
                result.Success = true;
                transaction.Commit();
                //else the transaction is already rolled back.
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                OnTransactionRollback(new ExceptionEventArgs(ex));
                OnDataException(new DataExceptionEventArgs(ex));
                result.Success = false;
                result.LastException = ex;
            }
        }
        /// <summary>
        /// Attempts to establish a connection to the specified data source.
        /// </summary>
        /// <param name="connection">The data connection instance.</param>
        /// <returns>
        /// <b>true</b> if the connection is established; otherwise, <b>false</b>.
        /// </returns>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        private bool TryConnect(IDbConnection connection)
        {
            bool returnValue = true;            //Return value.

            //Wait for the object if busy.
            TryWait(connection);

            //If the connection object is already connected, do nothing.
            if (connection.State != ConnectionState.Open)
            {
                //Try to reset the item if the connection state is broken.
                if (connection.State == ConnectionState.Broken)
                    TryClose(connection);

                //Attempt to establish a connection.
                try
                {
                    connection.Open();
                }
                catch (Exception ex)
                {
                    OnConnectionFailure(new ExceptionEventArgs(ex));
                    returnValue = false;
                }
            }

            return returnValue;
        }
        /// <summary>
        /// Closes the supplied connection object.
        /// </summary>
        /// <param name="connection">
        /// An <see cref="IDbConnection"/> instance to be closed.
        /// </param>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        public static void TryClose(IDbConnection connection)
        {
            if (connection != null)
            {
                try
                {
                    connection.Close();
                    connection.Dispose();
                }
                catch (Exception)
                {
                }
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="connection"></param>
        private static void TryWait(IDbConnection connection)
        {
            int startTime = 0;
            int endTime = 0;

            if (connection != null)
            {
                startTime = System.Environment.TickCount;
                endTime = startTime;
                while (((connection.State == ConnectionState.Connecting) ||
                       (connection.State == ConnectionState.Executing) ||
                       (connection.State == ConnectionState.Fetching)) && (endTime - startTime < 10000))
                {
                    System.Threading.Thread.Sleep(100);
                    endTime = System.Environment.TickCount;
                }
            }
        }
        #endregion
    }
}